package com.ulwx.tool.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ulwx.tool.CTime;

public class ExcelUtil {
	/**
	 * 写EXCEL文件
	 * 
	 * @param titlelist
	 * @param datalist
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public boolean writeExcel(String path, String title[], List datalist) {
		File f = new File(path);
		OutputStream out = null;

		if (f.isFile() && !f.canWrite()) {
			System.out.println("can not find file!");
			return false;
		}
		try {
			out = new FileOutputStream(f);
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("1");
			HSSFRow[] row = new HSSFRow[datalist.size() + 1];// 行
			HSSFCell[] valueCell = new HSSFCell[title.length];// 列
			row[0] = sheet.createRow(0);
			// 创建列头
			HSSFCell[] headerCell = new HSSFCell[title.length];
			for (int i = 0; i < title.length; i++) {
				headerCell[i] = row[0].createCell(i);
				headerCell[i].setCellValue(title[i]);
			}
			// 往列中写数据
			Iterator iterator = datalist.iterator();
			int n_row = 1;
			while (iterator.hasNext()) {
				String data[] = (String[]) iterator.next();
				row[n_row] = sheet.createRow(n_row);
				for (int n_cel = 0; n_cel < data.length; n_cel++) {
					valueCell[n_cel] = row[n_row].createCell(n_cel);
					valueCell[n_cel].setCellValue(data[n_cel]);
					System.out.println(valueCell[n_cel].getStringCellValue());
				}
				n_row++;
			}
			sheet.setGridsPrinted(false);
			wb.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
			return false;
		}

		return true;
	}

	@SuppressWarnings("unchecked")
	public List readExcel(String path) {
		return readExcel(path, 0);
	}

	/**
	 * 读EXCEL文件
	 * 
	 * @param path
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List readExcel(String path, int numSheets) {
		List list = new ArrayList();
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
			if (numSheets < 0 || numSheets >= workbook.getNumberOfSheets()
					|| workbook.getSheetAt(numSheets) == null)
				return null;
			// 表单
			HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet
			// 行
			for (int irow = 0; irow <= aSheet.getLastRowNum(); irow++) {
				if (null == aSheet.getRow(irow))
					continue;
				HSSFRow aRow = aSheet.getRow(irow);
				List al = new ArrayList();
				// 列
				for (int icel = 0; icel <= aRow.getLastCellNum(); icel++) {
					String strCell = "";
					if (null != aRow.getCell(icel)) {
						HSSFCell aCell = aRow.getCell(icel);
						try {
							if (aCell.getCellType() == (HSSFCell.CELL_TYPE_NUMERIC)) {
								strCell = String.valueOf(aCell
										.getNumericCellValue());
								int index = strCell.indexOf(".");
								if (index != -1) {
									strCell = strCell.substring(0, index);
								}
								if (HSSFDateUtil.isCellDateFormatted(aCell)) {
									strCell =DateFormat.getDateInstance().format( aCell.getDateCellValue() );
								}
							} else {
								strCell = aCell.getStringCellValue();
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
					// System.out.println(strCell);
					al.add(strCell);
				}
				// System.out.println("Excel cols : " + al.size());
				list.add(al);// 添加一行数据
			}
		} catch (Exception e) {
			System.out.println("-------------ReadExcelError------------------"
					+ e.getMessage());
			e.printStackTrace();
		}
		System.out.println("Excel Rows : " + list.size());
		return list;
	}

	@SuppressWarnings("unchecked")
	public List readExcelByTitle(String path) {
		return readExcelByTitle(path, 0);
	}

	/**
	 * 读EXCEL文件，根据列名 返回值：包含HashMap的List
	 * 
	 * @param path
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List readExcelByTitle(String path, int numSheets) {
		List list = new ArrayList();
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
			// 如果没有该Sheet，则返回NULL
			if (numSheets < 0 || numSheets >= workbook.getNumberOfSheets()
					|| workbook.getSheetAt(numSheets) == null)
				return null;

			// 获得该Sheet
			HSSFSheet aSheet = workbook.getSheetAt(numSheets);

			// 读取标题行
			HSSFRow aRow = aSheet.getRow(0);
			String title[] = new String[aRow.getLastCellNum()];
			//System.out.println(aSheet.getLastRowNum() + ","
			//		+ aRow.getLastCellNum());
			for (int icel = 0; icel < aRow.getLastCellNum(); icel++) {
				title[icel] = aRow.getCell(icel).getStringCellValue();
				//System.out.print(title[icel] + ',');
			}

			// 读取数据行
			for (int irow = 1; irow <= aSheet.getLastRowNum(); irow++) {
				// System.out.println("row" + irow + ":");
				if (null == aSheet.getRow(irow))
					continue;
				aRow = aSheet.getRow(irow);// 获取该行
				Map map = new HashMap();
				// 列
				for (int icel = 0; icel < aRow.getLastCellNum(); icel++) {
					String strCell = "";
					if (null != aRow.getCell(icel)) {
						HSSFCell aCell = aRow.getCell(icel);
						if (aCell.getCellType() == (HSSFCell.CELL_TYPE_NUMERIC)) {
							strCell = String.valueOf(aCell
									.getNumericCellValue());
							int index = strCell.indexOf(".");
							if (index != -1) {
								strCell = strCell.substring(0, index);
							}
							if (HSSFDateUtil.isCellDateFormatted(aCell)) {
								strCell = CTime.formatWholeAllDate(aCell
										.getDateCellValue());
							}
						} else {
							strCell = aCell.getStringCellValue();
						}
					}
					map.put(title[icel], strCell);
				}
				System.out.println("Excel cols : " + map.size());
				list.add(map);// 添加一行数据
			}
		} catch (Exception e) {
			System.out.println("-------------ReadExcelError------------------"
					+ e.getMessage());
			e.printStackTrace();
		}
		System.out.println("Excel Rows : " + list.size());
		return list;
	}

	@SuppressWarnings("unchecked")
	public static void main(String[] arg) {
		// 写入示例
		String title[] = { "厂商", "维保合同名称", "设备类型", "维保项目一", "维保项目二" };
		List datalist = new ArrayList();
		for (int i = 0; i < 6; i++) {
			String data[] = { "设备厂商" + i, "合同一" + i, "设备一" + i, "项目一" + i,
					"项目二" + i };
			datalist.add(data);
		}
		new ExcelUtil().writeExcel("d:\\2.xls", title, datalist);

	}
}
